package com.plugin.yongyou.util;

import java.sql.*;
import java.util.List;
import java.util.Map;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.shuohe.util.resultSet.Util;

//#      secondary:
//#          driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver
//#          url: jdbc:sqlserver://192.168.0.6:1433/UFDATA_888_20171
//#          username: CRM_test
//#          password: CRM_test    

public class ErpSqlUtil {

	private static Logger logger = LoggerFactory.getLogger(ErpSqlUtil.class);

	private final static String URL = "jdbc:sqlserver://192.168.0.6:1433;DatabaseName=UFDATA_888_2017";
	private static final String USER = "CRM_test";
	private static final String PASSWORD = "CRM_test";

	private static Connection conn = null;
	// 静态代码块（将加载驱动、连接数据库放入静态块中）
	static {
		try {
			// 1.加载驱动程序
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
			// 2.获得数据库的连接
			conn = (Connection) DriverManager.getConnection(URL, USER, PASSWORD);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	// 对外提供一个方法来获取数据库连接
	public static Connection getConnection() {
		if(conn == null)
		{
			try {
				// 1.加载驱动程序
				Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
				// 2.获得数据库的连接
				conn = (Connection) DriverManager.getConnection(URL, USER, PASSWORD);
			} catch (ClassNotFoundException e) {
				e.printStackTrace();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return conn;
	}

	public static int executeUpdate(String sql) throws SQLException
	{
		Connection connection = null;
		int ret= -1;
		try 
		{
			connection = getConnection();
			if(connection == null)
			{
				throw new Exception("数据库连接异常");
			}					
						
			ret = connection.prepareStatement(sql).executeUpdate();        	     	         
		}
		catch (SQLException e) 
		{
			logger.debug("Could not close JDBC Connection", e);
            e.printStackTrace();
        }
		catch (Exception e)
		{			
			logger.debug("Unexpected exception on closing JDBC Connection", e);
			e.printStackTrace();
		}
		finally
        {        	
        		connection.close();
        }
		return ret;
	}
	
	public static boolean execute(String sql) throws SQLException
	{
		Connection connection = null;
		boolean ret  = false;
		try 
		{
			connection = getConnection();
			if(connection == null)
			{
				throw new Exception("数据库连接异常");
			}					
						
			ret = connection.prepareStatement(sql).execute();        	     	         
		}
		catch (SQLException e) 
		{
			logger.debug("Could not close JDBC Connection", e);
            e.printStackTrace();
        }
		catch (Exception e)
		{			
			logger.debug("Unexpected exception on closing JDBC Connection", e);
			e.printStackTrace();
		}
		finally
        {        	
        		connection.close();
        }
		return ret;
	}
	/**
	 * 检索一条数据
	 * @author    秦晓宇
	 * @date      2017年10月31日 下午3:20:19 
	 * @param sql
	 * @return
	 * @throws SQLException
	 */
	public static Map<String,Object> getDataForOneItem(String sql) throws SQLException
	{ 
		Connection connection = null;
		Map<String,Object> ret=null;
		try 
		{
			connection = getConnection();
			if(connection == null)
			{
				throw new Exception("检索单条数据错误");
			}					
						
        	ResultSet result = connection.prepareStatement(sql).executeQuery();        	
        	ret = Util.ResultSetToMap(result);          	         
		}
		catch (SQLException e) 
		{
            e.printStackTrace();
        }
		catch (Exception e)
		{			
			e.printStackTrace();
		}
		finally
        {        	
        	connection.close();
        }
		return ret;
	}

	/**
	 * 检索多条数组数据
	 * @author    秦晓宇
	 * @date      2017年10月31日 下午3:21:00 
	 * @param sql
	 * @return
	 * @throws SQLException
	 */
	public static List<Map<String,Object>> getDataForList(String sql) throws SQLException
	{
		Connection connection = null;
		List<Map<String,Object>> ret=null;
		try 
		{
			connection = getConnection();
			if(connection == null)
			{
				throw new Exception("检索多条数据错误");
			}					
		
			System.out.println(sql);
        	ResultSet result = connection.prepareStatement(sql).executeQuery();        	
        	ret = Util.ResultSetToList(result);          	         
		}
		catch (SQLException e) 
		{
            e.printStackTrace();
        }
		catch (Exception e)
		{			
			e.printStackTrace();
		}
		finally
        {        	
        	connection.close();
        }
		return ret;
	}
	
	public static void closeConnection(Connection con) {
		if (con != null) {
			try {
				con.close();
			} catch (SQLException ex) {
				logger.debug("Could not close JDBC Connection", ex);
			} catch (Throwable ex) {
				// We don't trust the JDBC driver: It might throw RuntimeException or Error.
				logger.debug("Unexpected exception on closing JDBC Connection", ex);
			}
		}
	}

}
